/*
 * Project:     My Project Work, Copyright (C) 2011
 * Created at:  01.08.2011
 */
package de.myprojectwork.model;

import de.myprojectwork.common.util.ConfigurationEnum;
import de.myprojectwork.common.util.Logger;
import de.myprojectwork.common.util.MyProjectWorkException;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Diese Klasse stellt Methoden zur Verfügung, um die Datenbank zu erstellen, 
 * zu verändern oder zu löschen.
 * 
 * @author Chris Holdschuh
 */
public class DataDefinitionModelImpl extends AbstractModel implements DataDefinitionModel {

    public DataDefinitionModelImpl() {
        // leer
    }
    
    @Override
    public void deleteDb() throws IOException, MyProjectWorkException {
        // lösche DB File
        String dbFileUrl = ConfigurationModel.getValue(ConfigurationEnum.DB_FILE_URL);
        File dbFile = new File(dbFileUrl);
        if (dbFile != null) {
            boolean hasFileDelete = dbFile.delete();
            if (!hasFileDelete) {
                throw new IOException("DB-File konnte nicht gelöscht werden.");
            }
        } else {
            throw new IOException("DB-File konnte nicht gelöscht werden. Die Datei '" + dbFileUrl 
                    + "' wurde nicht gefunden.");
        }
        // lösche Trace File
        String dbTraceFileUrl = ConfigurationModel.getValue(ConfigurationEnum.DB_TRACE_FILE_URL);
        File dbTraceFile = new File(dbTraceFileUrl);
        if (dbTraceFile != null) {
            boolean hasFileDelete = dbTraceFile.delete();
            if (!hasFileDelete) {
                Logger.error("Die DB-Trace-File '" + dbTraceFileUrl + "' wurde nicht gelöscht.");
            }
        } else {
             Logger.error("Die DB-Trace-File '" + dbTraceFileUrl + "' wurde nicht gelöscht.");
        }
    }
    
    @Override
    public void deleteTable(Table table) throws MyProjectWorkException {
        try (Connection conn = getConnection()) {
            execute("DROP TABLE " + table.name(), conn);
        } catch (SQLException ex) {
            Logger.error("Fehler beim Löschen der Tabellen.", ex);
            throw new MyProjectWorkException("Fehler beim Löschen der Tabellen.", ex);
        } 
    }
    
    @Override
    public void deleteEntries(Table table) throws MyProjectWorkException {
        try (Connection conn = getConnection()) {
            execute("DELETE FROM " + table.name(), conn);
        } catch (SQLException ex) {
            Logger.error("Fehler beim Löschen der Einträge.", ex);
            throw new MyProjectWorkException("Fehler beim Löschen der Einträge.", ex);
        } 
    }
    
    @Override
    public void createSequence() throws MyProjectWorkException {
        try (Connection conn = getConnection()) {
            execute("CREATE SEQUENCE SEQ_ID CACHE 0", conn);
        } catch (SQLException ex) {
            Logger.error("Fehler beim Erstellen der Sequenz.", ex);
            throw new MyProjectWorkException("Fehler beim Erstellen der Sequenz.", ex);
        } 
    }
    
    @Override
    public void deleteSequence() throws MyProjectWorkException {
        try (Connection conn = getConnection()) {
            execute("DROP SEQUENCE SEQ_ID", conn);
        } catch (SQLException ex) {
            Logger.error("Fehler beim Löschen der Sequenz.", ex);
            throw new MyProjectWorkException("Fehler beim Löschen der Sequenz.", ex);
        } 
    }
    
    @Override
    public void createTable(Table table) throws MyProjectWorkException {
        if (table != null) {
            String sqlCommand = null;
            switch (table) {
                case CONFIGURATION:
                    sqlCommand = "CREATE TABLE " + table 
                            + " ("
                            + "ID       INT(10)         PRIMARY KEY,"
                            + "KEY      VARCHAR(1000)   NOT NULL UNIQUE,"
                            + "VALUE    VARCHAR(1000)   NOT NULL,"
                            + "UPDATE   TIMESTAMP       NOT NULL"
                            + ")";
                    break;
                case PROJECT:
                    sqlCommand = "CREATE TABLE " + table 
                            + " ("
                            + "ID               INT(10)         PRIMARY KEY,"
                            + "NUMBER           INT(10),"
                            + "NAME             VARCHAR(255),"
                            + "CUSTOMER         VARCHAR(255),"
                            + "DATESTART        DATE,"
                            + "DATEEND          DATE,"
                            + "STATUSNAME       VARCHAR(222)    NOT NULL,"
                            + "STATUSPERCENT    INT(10),"
                            + "UPDATE           TIMESTAMP       NOT NULL,"
                            + "CONSTRAINT " + Table.PROJECT + "_UNQ UNIQUE (NUMBER, NAME, CUSTOMER)"
                            + ")";
                    break;
                case TODO:
                    sqlCommand = "CREATE TABLE " + table 
                            + " ("
                            + "ID               INT(10)         PRIMARY KEY,"
                            + "FKPROJECT        INT(10)         NOT NULL REFERENCES " + Table.PROJECT + " (ID),"
                            + "FKPARENT         INT(10)         REFERENCES " + table + " (ID),"
                            + "ORDERNUMBER      INT(10)         NOT NULL,"
                            + "TARGETDATE       DATE,"
                            + "STATUSPERCENT    INT(10),"
                            + "TEXT             VARCHAR(255)    NOT NULL,"
                            + "UPDATE           TIMESTAMP       NOT NULL,"
                            + "CONSTRAINT " + table + "_UNQ_ORDER_NUMBER UNIQUE (FKPROJECT, FKPARENT, ORDERNUMBER),"
                            + "CONSTRAINT " + table + "_UNQ_TEXT UNIQUE (FKPROJECT, TEXT)"
                            + ")";
                    break;
                case ACTIVITY_CATEGORY:
                    sqlCommand = "CREATE TABLE " + table 
                            + " ("
                            + "ID           INT(10)         PRIMARY KEY,"
                            + "FKPROJECT    INT(10)         NOT NULL REFERENCES " + Table.PROJECT + " (ID),"
                            + "FKPARENT     INT(10)         REFERENCES " + table + " (ID),"
                            + "NAME         VARCHAR(255)    NOT NULL,"
                            + "UPDATE       TIMESTAMP       NOT NULL,"
                            + "CONSTRAINT " + table + "_UNQ UNIQUE (FKPROJECT, FKPARENT, NAME)"
                            + ")";
                    break;
                case HISTORY_ACTIVITY:
                    sqlCommand = "CREATE TABLE " + table 
                            + " ("
                            + "ID                       INT(10)         PRIMARY KEY,"
                            + "FKPROJECT                INT(10)         NOT NULL REFERENCES " + Table.PROJECT + " (ID),"
                            + "FKACTIVITYCATEGORY       INT(10)         REFERENCES " + Table.ACTIVITY_CATEGORY + " (ID),"
                            + "DATE                     DATE            NOT NULL,"
                            + "TIMESTART                TIMESTAMP,"
                            + "TIMEEND                  TIMESTAMP,"
                            + "DURATION                 INT(10),"
                            + "TEXT                     CLOB,"
                            + "UPDATE                   TIMESTAMP       NOT NULL"
                            + ")";
                    break;
                case RADAR:
                    sqlCommand = "CREATE TABLE " + table 
                            + " ("
                            + "ID           INT(10)         PRIMARY KEY,"
                            + "FKPROJECT    INT(10)         NOT NULL REFERENCES " + Table.PROJECT + " (ID),"
                            + "ORDERNUMBER  INT(10)         NOT NULL,"
                            + "HEADLINE     VARCHAR(255)    NOT NULL,"
                            + "UPDATE       TIMESTAMP       NOT NULL,"
                            + "TEXT         CLOB,"
                            + "CONSTRAINT " + table + "_UNQ_HEADLINE UNIQUE (FKPROJECT, HEADLINE),"
                            + "CONSTRAINT " + table + "_UNQ_ORDER_NUMBER UNIQUE (FKPROJECT, ORDERNUMBER)"
                            + ")";
                    break;
                case TAG:
                    sqlCommand = "CREATE TABLE " + table 
                            + " ("
                            + "ID           INT(10)         PRIMARY KEY,"
                            + "FKPROJECT    INT(10)         NOT NULL REFERENCES " + Table.PROJECT + " (ID),"
                            + "NAME         VARCHAR(255)    NOT NULL,"
                            + "CONSTRAINT " + table + "_UNQ_NAME UNIQUE (FKPROJECT, NAME)"
                            + ")";
                    break;
                case LINK:
                    sqlCommand = "CREATE TABLE " + table 
                            + " ("
                            + "ID           INT(10)         PRIMARY KEY,"
                            + "FKPARENT     INT(10)         NOT NULL,"
                            + "FKCHILD      INT(10)         NOT NULL,"
                            + "TABLENAME    VARCHAR(255)    NOT NULL,"
                            + "CONSTRAINT " + table + "_UNQ UNIQUE (FKPARENT, FKCHILD, TABLENAME)"
                            + ")";
                    break;
                default:
                    throw new MyProjectWorkException("Tabelle " + table + " wird nicht unterstützt.");
                
            }
            try (Connection conn = getConnection()) {
                execute(sqlCommand, conn);
                
            } catch (SQLException ex) {
                Logger.error("Fehler beim Löschen der Sequenz.", ex);
                throw new MyProjectWorkException("Fehler beim Löschen der Sequenz.", ex);
            }
        } else {
            Logger.error("Ungültiger Parameter übergeben.");
            throw new IllegalArgumentException("Ungültiger Parameter übergeben.");
        }
    }

    @Override
    public void deleteTables() throws MyProjectWorkException {
        deleteTable(Table.CONFIGURATION);
        deleteTable(Table.RADAR);
        deleteTable(Table.LINK);
        deleteTable(Table.TODO);
        deleteTable(Table.TAG);
        deleteTable(Table.HISTORY_ACTIVITY);
        deleteTable(Table.ACTIVITY_CATEGORY);
        deleteTable(Table.PROJECT);
    }

    @Override
    public void createTables() throws MyProjectWorkException {
        createTable(Table.CONFIGURATION);
        createTable(Table.PROJECT);
        createTable(Table.ACTIVITY_CATEGORY);
        createTable(Table.HISTORY_ACTIVITY);
        createTable(Table.RADAR);
        createTable(Table.LINK);
        createTable(Table.TODO);
        createTable(Table.TAG);
    }

    @Override
    public List<String> showTables() throws MyProjectWorkException {
        List<String> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement("SHOW TABLES");
            rs = ps.executeQuery();
            while(rs.next()) {
                list.add(rs.getString(1));
            }
            
        } catch (SQLException ex) {
            throw new MyProjectWorkException(ex.getMessage(), ex);
        } finally {
            close(conn);
            close(ps);
            close(rs);
        }
        return list;
    }
}
